package com.sea.finance.service.impl;

import com.sea.finance.entity.VtFeeOriginally;
import com.sea.finance.mapper.VtFeeOriginallyMapper;
import com.sea.finance.service.VtFeeOriginallyService;
import com.sea.finance.service.VtService;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @author 陈鸿飞
 */
@Service
public class VtFeeOriginallyServiceImpl implements VtFeeOriginallyService {
    @Autowired
    private VtFeeOriginallyMapper vtFeeOriginallyMapper;
    @Autowired
    private VtService vtService;
    @Value("${db.url}")
    private String JDBC_URL;

    @Value("${db.username}")
    private String USER;

    @Value("${db.password}")
    private String PASSWORD;

    private Integer BATCH_SIZE=1000;

    @Override
    public Integer save(VtFeeOriginally vtFeeOriginally) {
        return vtFeeOriginallyMapper.insert(vtFeeOriginally);
    }

    @Override
    public List<VtFeeOriginally> findByCnd(VtFeeOriginally vtFeeOriginally) {
        return vtFeeOriginallyMapper.findByCnd(vtFeeOriginally);
    }

    @Override
    public VtFeeOriginally originally(VtFeeOriginally vtFeeOriginally) throws Exception{
        StringBuilder sql = new StringBuilder();
        StringBuilder fileName = new StringBuilder();
        sql.append("select * from a_vt where shop_name='" + vtFeeOriginally.getShopName() + "' and fee_name in( select fee_name from a_vt_fee_match a where a.fee_type='" + vtFeeOriginally.getFeeType() + "') and period='"+vtFeeOriginally.getPeriod()+"'");
         fileName.append(vtFeeOriginally.getPeriod()+"-"+vtFeeOriginally.getShopName());
        if(StringUtils.isNotBlank(vtFeeOriginally.getBrandName())){
            sql.append("  and brand_name='" + vtFeeOriginally.getBrandName() + "'");
            fileName.append("-"+vtFeeOriginally.getBrandName());
        }
        if(StringUtils.isNotBlank(vtFeeOriginally.getCountryName())){
            sql.append("  and country_name='" + vtFeeOriginally.getCountryName() + "'");
            fileName.append("-"+vtFeeOriginally.getCountryName());
        }
        if(StringUtils.isNotBlank(vtFeeOriginally.getListingName())){
            sql.append(" and listing_name= '"+vtFeeOriginally.getListingName()+"'");
            fileName.append("-"+vtFeeOriginally.getListingName());
        }
        if(StringUtils.isNotBlank(vtFeeOriginally.getSku())){
            sql.append(" and sku='"+vtFeeOriginally.getSku()+"'");
            fileName.append("-"+vtFeeOriginally.getSku());
        }
        fileName.append("-"+vtFeeOriginally.getFeeType()+".csv");

        System.out.println(sql.toString());
        System.out.println(fileName.toString());
        if(StringUtils.isBlank(sql)){
            throw new RuntimeException("系统异常!请联系管理员!");
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        BufferedWriter writer = null;
        ByteArrayOutputStream byteArrayOutputStream = null;


        // 获取数据库连接
        conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD);
        pstmt = conn.prepareStatement(sql.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        // 关闭自动提交
        conn.setAutoCommit(false);
        // MySQL特有的设置，开启流式查询
        pstmt.setFetchSize(Integer.MIN_VALUE);

        rs = pstmt.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        // 创建字节数组输出流，用于在内存中存储CSV数据
        byteArrayOutputStream = new ByteArrayOutputStream();
        // 创建字符缓冲写入器
        writer = new BufferedWriter(new OutputStreamWriter(byteArrayOutputStream, "UTF-8"));

        // 写入表头
        List<String> header = new ArrayList<>();
        for (int i = 1; i <= columnCount; i++) {
            header.add(metaData.getColumnName(i));
        }
        writer.write(String.join(",", header));
        writer.newLine();

        // 批量写入数据
        int count = 0;
        while (rs.next()) {
            List<String> row = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                Object value = rs.getObject(i);
                // 处理包含逗号的值，用双引号包裹
                String valueStr = value != null ? value.toString() : "";
                if (valueStr.contains(",")) {
                    valueStr = "\"" + valueStr + "\"";
                }
                row.add(valueStr);
            }
            writer.write(String.join(",", row));
            writer.newLine();

            // 每BATCH_SIZE行刷新一次，减少内存占用
            if (++count % BATCH_SIZE == 0) {
                writer.flush();
                System.out.println("已处理 " + count + " 条数据");
            }
        }

        // 最后一次刷新，确保所有数据都写入输出流
        writer.flush();
        System.out.println("处理完成，共 " + count + " 条数据");
        vtFeeOriginally.setTotalNumber(count);
        // 将字节数组输出流转换为输入流并返回
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
        vtFeeOriginally.setFileUrl(vtService.uploadVtFile(fileName.toString(), byteArrayInputStream));
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
        if (writer != null) writer.close();
        if (byteArrayOutputStream != null) byteArrayOutputStream.close();
        return vtFeeOriginally;
        // 关闭资源
    }

    @Override
    public Integer usave(VtFeeOriginally vtFeeOriginally) {
        return vtFeeOriginallyMapper.update(vtFeeOriginally);
    }
}
